package com.gopay.cron.dao.trans.impl;

import java.io.Serializable;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.stereotype.Repository;

import com.gopay.common.base.dao.impl.BaseDAOImpl;
import com.gopay.common.constants.trans.TxnStaCode;
import com.gopay.cron.dao.trans.MainOrderQueryDAO;
import com.gopay.scheduler.job.WalletCheckTimeoutOrderJob;


/**
 * MainOrderDAOImpl.java
 * @author pengxw
 * @2013-5-22 上午9:25:44  www.gopay.com.cn Inc.All rights reserved.
 */
@Repository("mainOrderQueryDAO")
@SuppressWarnings("all")
public class MainOrderQueryDAOImpl extends BaseDAOImpl<Object[], Serializable>
		implements MainOrderQueryDAO {	
	private Logger logger = LoggerFactory
            .getLogger(MainOrderQueryDAOImpl.class);
	// 国付宝收款类SQL语句 >>暂时收款类的线下充值需要审核
	private static final String revMoneySql = 
		 " select * from ( (select     t1.GOPAY_TXN_TM GOPAY_TXN_TM, "
			+ "          t1.ORDER_STLM_TIME order_stlm_time, "
			+ "         t1.MER_ORDER_ID mer_order_id, "
			+ "        t1.GOPAY_ORDER_ID gopay_order_id, "
			+ "         t5.cust_name trans_adr, "
			+ "        t1.MER_ID mer_id, "
			+ "       t1.MER_TXN_AMT order_deal_amt, " //t1.ORDER_DEAL_AMT 
			+ "       t1.ORDER_REAL_FEE_AMT order_real_fee_amt, "
			+ "       (case "
            + "       when t1.OUT_STLM_ID is not null then "
            + "       (select b1.bank_name "
            + "          from bis_bank_info_main b1 "
            + "        where t1.OUT_STLM_ID = b1.BANK_CODE) "
            + "     else "
            + "     '' "
            + " end)   main_pay_acct , "//付款银行名称
            + "  (case "
            + "   when t1.GOPAY_ORDER_ID is not null then "
            + "    (select p1.recv_bank_name "
            + "     from CPS_GEN_PAY_ORDER p1 "
            + "     where t1.GOPAY_ORDER_ID = p1.gopay_order_id) "
            + "   else "
            + "     '' "
            + "  end)  main_recv_acct_prod_name, " //收款银行名称        
			+ " (case  "
            + "       when t1.GOPAY_ORDER_ID is not null  then "
			
            //-----------------------------------------------
            //edit by fanghw 20140411
            //临时解决银行订单表同一国付宝订单存在2条以上记录问题 
            
            /*
            + "        (select b10.GOPAY_OUT_ORDER_ID "
            + "           from bis_bank_main_order b10 "
            + "        where t1.GOPAY_ORDER_ID = "
            + "              b10.GOPAY_ORDER_ID) "
            */
            
            +" (select  b10.GOPAY_OUT_ORDER_ID "
            +" from bis_bank_main_order b10 "
            +" where  "
            +" not exists (select 1 from bis_bank_main_order bmo where bmo.created_ts>b10.created_ts and b10.gopay_order_id=bmo.gopay_order_id ) "
            +" and t1.GOPAY_ORDER_ID = b10.GOPAY_ORDER_ID) "
            
            // end edit
            //-----------------------------------------------
            
            + "    else "
            + "    '' "
            + "  end) gopay_out_order_id, "
			+ "  (case "
			+ "   when t1.PAY_CUST_ID is not null and t1.GOPAY_INT_TXN_CD not in('00500') then "
			+ "   (select t3.EMAIL "
			+ "      from GOPAY_ALL_CUST_INFO t3 "
			+ "   where t1.PAY_CUST_ID = t3.CUST_ID "
			+ "    ) "
			+ "    else "
			+ "  '' "
			+ " end) pay_cus_email, "
			//+ "  t1.PAY_ACCT pay_acct, "
			+ "(case when t1.PAY_ACCT is not null and t1.GOPAY_INT_TXN_CD  not in('00500') "
			+ " then t1.PAY_ACCT "
            + " else '' "
            + " end "
            + " )pay_acct, "
			+ " (case "
			+ "   when t1.MAIN_RECV_CUST_ID is not null then "
			+ "   (select t4.CUST_NAME "
			+ "     from GOPAY_ALL_CUST_INFO t4 "
			+ "   where t1.MAIN_RECV_CUST_ID = t4.CUST_ID "
			+ "      ) "
			+ "    else "
			+ "    '' "
			+ "  end) rec_cus_name, "
			+ "  (case "
			+ "   when t1.MAIN_RECV_CUST_ID is not null then "
			+ "   (select t4.EMAIL "
			+ "      from GOPAY_ALL_CUST_INFO t4 "
			+ "     where t1.MAIN_RECV_CUST_ID = t4.CUST_ID "
			+ "     ) "
			+ "  else "
			+ "  '' "
			+ "  end) rec_cus_mail, "
			+ " t1.MAIN_RECV_ACCT main_recv_acct, "
			+ "  (case when t1.PAY_ACCT is not null and t1.GOPAY_INT_TXN_CD not in('00500') then (select acc.PROD_CODE from ACPS_ACCT_MAST acc where acc.ACCT_ID=t1.PAY_ACCT)else '' end )acct_type, "
			+ "  t1.GOPAY_INT_TXN_CD GOPAY_INT_TXN_CD, "
			+ "  t1.GATEWAY_SOURCE order_ori, "
			+ "  t1.TXN_STA_CD trans_status, "
			+ "  '' hander_status, "
			+ "  '' options, "
			+ "  t1.CREATED_TS CREATED_TS, "
			+ "  (case  when t1.PAY_CUST_ID is not null and t1.GOPAY_INT_TXN_CD not in ('00500') then (select t0.CUST_NAME from GOPAY_ALL_CUST_INFO t0 where t1.PAY_CUST_ID= t0.CUST_ID) else  ''  end)pay_cus_name , "
			+ "  -1 step_num,   "//
            + "  -1  decison,   "//
            + "  -1  done ,  "//
            + "  t1.PAY_CHANNEL  pay_channel ,  "//
            + "  t1.REVERSAL_FLAG  reversal_flag   "//
            //+ "  t1.MER_ORDER_ID  MER_ORDER_ID  "//
			+ "   from CPS_GEN_MAIN_ORDER  t1, "			
			+ "      CPS_GEN_RECEIPT_ORDER  t0, "
			+ "       GOPAY_ALL_CUST_INFO t5 "				
			+ "   where not exists"
            + "    (select *  "
            + "   from CPS_GEN_MAIN_ORDER t01 "
            + "   where t1.GOPAY_ORDER_ID = t01.GOPAY_ORDER_ID "
            + "  and t1.pay_channel not in('00','01','03','04','51') "
            + "   and t1.GOPAY_INT_TXN_CD = '00500') "//过滤线下充值，已经在审核数据 wf_appr_view | wf_appr_task_tdl 中有数据
			+ " and  t1.GOPAY_ORDER_ID = t0.GOPAY_ORDER_ID "					
			+ "   and t1.MER_ID = t5.CUST_ID "							
			//+ "   and t1.REVERSAL_FLAG=100 "		
			
			+" and t1.GOPAY_INT_TXN_CD not in('00207') "
			
			+ "   ))tt where 1=1 ";

	// 国付宝付款类的SQL语句
	private static final String payMoneySql = 
			 "select * from ( (select t1.GOPAY_TXN_TM GOPAY_TXN_TM, "
			+ "          t1.ORDER_STLM_TIME order_stlm_time, "
			+ "         t1.MER_ORDER_ID mer_order_id, "
			+ "        t1.GOPAY_ORDER_ID gopay_order_id, "
			+ "         t5.cust_name trans_adr, "
			+ "        t1.MER_ID mer_id, "
			+ "       t1.MER_TXN_AMT order_deal_amt, "
			+ "       t1.ORDER_REAL_FEE_AMT order_real_fee_amt, "
			+ "       (case "
			+ "       when t1.OUT_STLM_ID is not null then "
			+ "       (select b1.bank_name "
			+ "          from bis_bank_info_main b1 "
			+ "        where t1.OUT_STLM_ID = b1.BANK_CODE) "
			+ "     else "
			+ "     '' "
			+ " end) main_pay_acct, "
			//+ " T2.RECV_BANK_NAME  main_recv_acct_prod_name, "
			+ " ''  main_recv_acct_prod_name, "
			+" (case "
	        +"        when t1.GOPAY_ORDER_ID is not null and t1.GOPAY_INT_TXN_CD not in('01200','01201','01203') then "
	       
	      //-----------------------------------------------
            //edit by fanghw 20140411
            //临时解决银行订单表同一国付宝订单存在2条以上记录问题 
            
            /*
            +"         (select   b10.GOPAY_OUT_ORDER_ID "
            +"           from bis_bank_main_order b10 "
            + "         where t1.GOPAY_ORDER_ID = b10.GOPAY_ORDER_ID)  " 
            */
            
            +" (select  b10.GOPAY_OUT_ORDER_ID "
            +" from bis_bank_main_order b10 "
            +" where  "
            +" not exists (select 1 from bis_bank_main_order bmo where bmo.created_ts>b10.created_ts and b10.gopay_order_id=bmo.gopay_order_id ) "
            +" and t1.GOPAY_ORDER_ID = b10.GOPAY_ORDER_ID) "
            
            // end edit
            //-----------------------------------------------
	        
	           
	        
	        
	        
	        
	        +"  else "
	        +"  '' "
	         + " end) gopay_out_order_id, "
			+ "  (case "
			+ "   when t1.PAY_CUST_ID is not null then "
			+ "   (select t3.EMAIL "
			+ "      from GOPAY_ALL_CUST_INFO t3 "
			+ "   where t1.PAY_CUST_ID = t3.CUST_ID "
			+ "    ) "
			+ "    else "
			+ "  '' "
			+ " end) pay_cus_email, "
			+ "  t1.PAY_ACCT pay_acct, "
			+ " (case "
			+ "   when t1.MAIN_RECV_CUST_ID is not null then "
			+ "   (select t4.CUST_NAME "
			+ "     from GOPAY_ALL_CUST_INFO t4 "
			+ "   where t1.MAIN_RECV_CUST_ID = t4.CUST_ID "
			+ "      ) "
			+ "    else "
			+ "    '' "
			+ "  end) rec_cus_name, "
			+ "  (case "
			+ "   when t1.MAIN_RECV_CUST_ID is not null then "
			+ "   (select t4.EMAIL "
			+ "      from GOPAY_ALL_CUST_INFO t4 "
			+ "     where t1.MAIN_RECV_CUST_ID = t4.CUST_ID "
			+ "     ) "
			+ "  else "
			+ "  '' "
			+ "  end) rec_cus_mail, "
			+ " t1.MAIN_RECV_ACCT main_recv_acct, "
			+ "  (case when t1.PAY_ACCT is not null then (select acc.PROD_CODE from ACPS_ACCT_MAST acc where acc.ACCT_ID=t1.PAY_ACCT)else '' end )acct_type, "
			+ "  t1.GOPAY_INT_TXN_CD GOPAY_INT_TXN_CD, "
			+ "  t1.GATEWAY_SOURCE order_ori, "
			+ "  t1.TXN_STA_CD trans_status, "
			+ "  '' hander_status, "
			+ "  '' options, "
			+ "  t1.CREATED_TS CREATED_TS, "
			+ "  (case  when t1.PAY_CUST_ID is not null then (select t0.CUST_NAME from GOPAY_ALL_CUST_INFO t0 where t1.PAY_CUST_ID= t0.CUST_ID) else  ''  end)pay_cus_name ,  "
			+ "  -1 step_num,   "//
	        + "  -1  decison,   "//
	        + "  -1  done , "//
	        + "  t1.PAY_CHANNEL  pay_channel ,  "//
	        + "  t1.REVERSAL_FLAG  reversal_flag  "//
          //  + "  t1.MER_ORDER_ID  MER_ORDER_ID  "//
			+ "   from CPS_GEN_MAIN_ORDER  t1, "
			+ "      CPS_GEN_PAY_ORDER     t2, "
			+ "      GOPAY_ALL_CUST_INFO t5 "					
			+ "   where not exists "
            + "    (select *     "  
            + "             from CPS_GEN_MAIN_ORDER t01 "
            + "             where  t1.GOPAY_ORDER_ID= t01.GOPAY_ORDER_ID "
            + "              and t1.PAY_CHANNEL in('52') "
            + "             and t1.GOPAY_INT_TXN_CD='00900') "
			+ "    and t1.GOPAY_ORDER_ID = t2.GOPAY_ORDER_ID "
			+ "    and t1.MER_ID = t5.CUST_ID "				
			+ "    and t1.REVERSAL_FLAG=100 "							   			
			+ "   and t1.GOPAY_INT_TXN_CD not in ('00700','00800','00207') "
			+ "  ))tt where 1=1  ";
	
	
	   // 国付宝辅助类的 退款,担保支付取消（需要审核 这个sql是过滤掉需要审核的 要审核的都在auditTranSql语句中）现在辅助类的交易只有退款和担保支付交易>>暂时的
    private static final String assistSql = 
             "select * from ( (select t1.GOPAY_TXN_TM GOPAY_TXN_TM, "
            + "          t1.ORDER_STLM_TIME order_stlm_time, "
            + "         t1.MER_ORDER_ID mer_order_id, "
            + "        t1.GOPAY_ORDER_ID gopay_order_id, "
            + "         t5.cust_name trans_adr, "
            + "        t1.MER_ID mer_id, "
            + "       t1.MER_TXN_AMT order_deal_amt, "
//            + "       t1.ORDER_REAL_FEE_AMT order_real_fee_amt, "
            + "       DECODE(T1.GOPAY_INT_TXN_CD,"
            + "        '01200',"
            + "        T1.ORDER_REAL_FEE_AMT - T2.REFUND_FEE_AMT,"
             + "       T1.ORDER_REAL_FEE_AMT) ORDER_REAL_FEE_AMT,"
            + "       (case "
            + "       when t1.OUT_STLM_ID is not null then "
            + "       (select b1.bank_name "
            + "          from bis_bank_info_main b1 "
            + "        where t1.OUT_STLM_ID = b1.BANK_CODE) "
            + "     else "
            + "     '' "
            + " end) main_pay_acct, "
            + " '' main_recv_acct_prod_name , "
            + "  '' gopay_out_order_id, "
            + " (case "
            + "        when t1.PAY_CUST_ID is not null and t1.GOPAY_INT_TXN_CD not in ('00401','00400') then    "
            + "         (select t3.EMAIL "
            + "            from GOPAY_ALL_CUST_INFO t3   "
            + "            where t1.PAY_CUST_ID = t3.CUST_ID)   "
            + "          else   "
            + "          ''  "
            + "      end) pay_cus_email,  "
            + "  (case   "
            + "         when t1.PAY_ACCT is not null  and t1.GOPAY_INT_TXN_CD not in('00401','00402') then  "
            + "          (t1.PAY_ACCT)  "
            + "             when t1.GOPAY_INT_TXN_CD ='00402' then(  "
            + "             select rel.ACCT_ID  from ACPS_SCHEMA_ACCT_REL rel where rel.GOPAY_INT_TXN_CD='00402' and rel.SCHEMA_CODE='2202' "
            + "               ) "
            + "              when t1.GOPAY_INT_TXN_CD ='00401' then( "
            + "              select rel.ACCT_ID  from ACPS_SCHEMA_ACCT_REL rel where rel.GOPAY_INT_TXN_CD='00401' and rel.SCHEMA_CODE='2801'  "
            + "              ) "
            + "          else  "
            + "           ''  "
            + "       end) pay_acct,  "
            + " (case "
            + "   when t1.MAIN_RECV_CUST_ID is not null then "
            + "   (select t4.CUST_NAME "
            + "     from GOPAY_ALL_CUST_INFO t4 "
            + "   where t1.MAIN_RECV_CUST_ID = t4.CUST_ID "
            + "      ) "
            + "    else "
            + "    '' "
            + "  end) rec_cus_name, "
            + "  (case "
            + "   when t1.MAIN_RECV_CUST_ID is not null then "
            + "   (select t4.EMAIL "
            + "      from GOPAY_ALL_CUST_INFO t4 "
            + "     where t1.MAIN_RECV_CUST_ID = t4.CUST_ID "
            + "     ) "
            + "  else "
            + "  '' "
            + "  end) rec_cus_mail, "
            + " t1.MAIN_RECV_ACCT main_recv_acct, "
            + " (case  "
            + "        when t1.PAY_ACCT is not null  and t1.GOPAY_INT_TXN_CD not in('00401','00402') then"
            + "         (select acc.PROD_CODE "
            + "            from ACPS_ACCT_MAST acc " 
            + "            where acc.ACCT_ID = t1.PAY_ACCT) "
            + "          when t1.GOPAY_INT_TXN_CD ='00402' then( "
            + "           select  (select ma1.PROD_CODE from ACPS_ACCT_MAST ma1 where ma1.ACCT_ID=rel.ACCT_ID)  from ACPS_SCHEMA_ACCT_REL rel where rel.GOPAY_INT_TXN_CD='00402' and rel.SCHEMA_CODE='2202'  "
            + "              ) "
            + "             when t1.GOPAY_INT_TXN_CD ='00401' then( "
            + "              select (select ma1.PROD_CODE from ACPS_ACCT_MAST ma1 where ma1.ACCT_ID=rel.ACCT_ID)  from ACPS_SCHEMA_ACCT_REL rel where rel.GOPAY_INT_TXN_CD='00401' and rel.SCHEMA_CODE='2801'  "
            + "               ) "
            + "          else "
            + "           '' "
            + "       end) acct_type, "
            + "  t1.GOPAY_INT_TXN_CD GOPAY_INT_TXN_CD, "
            + "  t1.GATEWAY_SOURCE order_ori, "
            + "  t1.TXN_STA_CD trans_status, "
            + "  '' hander_status, "
            + "  '' options, "
            + "  t1.CREATED_TS CREATED_TS, "
            + " (case "
            + "         when t1.PAY_CUST_ID is not null  and t1.GOPAY_INT_TXN_CD not in('00401','00402') then "
            + "          (select t0.CUST_NAME "
            + "             from GOPAY_ALL_CUST_INFO t0 "
            + "           where t1.PAY_CUST_ID = t0.CUST_ID) "
            + "           when t1.GOPAY_INT_TXN_CD ='00402' then( "
            + "            select (select ma1.ACCT_NAME from ACPS_ACCT_MAST ma1 where ma1.ACCT_ID=rel.ACCT_ID)  from ACPS_SCHEMA_ACCT_REL rel where rel.GOPAY_INT_TXN_CD='00402' and rel.SCHEMA_CODE='2202' " 
            + "             ) "
            + "            when t1.GOPAY_INT_TXN_CD ='00401' then( "
            + "            select (select  ma1.ACCT_NAME from ACPS_ACCT_MAST ma1 where ma1.ACCT_ID=rel.ACCT_ID)  from ACPS_SCHEMA_ACCT_REL rel where rel.GOPAY_INT_TXN_CD='00401' and rel.SCHEMA_CODE='2801'  "
            + "           ) "
            + "        else "
            + "         '' "
            + "     end) pay_cus_name, "
            + "  -1 step_num,   "//
            + "  -1  decison,   "//
            + "  -1  done ,  "//
            + "  t1.PAY_CHANNEL  pay_channel ,  "//
            + "  t1.REVERSAL_FLAG  reversal_flag  "//
           // + "  t1.MER_ORDER_ID  MER_ORDER_ID  "//
            + "   from CPS_GEN_MAIN_ORDER  t1, "
            + "      CPS_GEN_ASSIST_ORDER     t2, "
            + "      GOPAY_ALL_CUST_INFO t5 "                                   
           // + "   where   not exists "
           // + " (select * "
           // + "        from CPS_GEN_MAIN_ORDER t00 "
           // + "        where t1.RELEVANCE_GOPAY_ORDER_ID = "
           // + "             t00.GOPAY_ORDER_ID "
            //+ "          and t00.PAY_CHANNEL  not in  ('00', '02', '52','50','99')) "
			//过滤掉要审核的退款
			+ "    where (select t1.PAY_CHANNEL from CPS_GEN_MAIN_ORDER t0 where t1.RELEVANCE_GOPAY_ORDER_ID = t0.GOPAY_ORDER_ID)  in ('00', '02', '51') "
            + "    and t1.GOPAY_ORDER_ID = t2.GOPAY_ORDER_ID "
            + "    and t1.MER_ID = t5.CUST_ID "                       
           // + "     and t1.REVERSAL_FLAG=100 "  
            +"    and t1.GOPAY_INT_TXN_CD not in('00207') "
            + "  ))tt where 1=1  ";
	
	
	//关联账户转账 sql
	private static final String transferAcctSql=
             " select * from ( (select t1.GOPAY_TXN_TM GOPAY_TXN_TM, "
            + "          t1.ORDER_STLM_TIME order_stlm_time, "
            + "         t1.MER_ORDER_ID mer_order_id, "
            + "        t1.GOPAY_ORDER_ID gopay_order_id, "
            + "         t5.cust_name trans_adr, "
            + "        t1.MER_ID mer_id, "
            + "       t1.MER_TXN_AMT order_deal_amt, "
            + "       t1.ORDER_REAL_FEE_AMT order_real_fee_amt, "
            + "       (case "
            + "       when t1.OUT_STLM_ID is not null then "
            + "       (select b1.bank_name "
            + "          from bis_bank_info_main b1 "
            + "        where t1.OUT_STLM_ID = b1.BANK_CODE) "
            + "     else "
            + "     '' "
            + " end) main_pay_acct, "
            + " ''  main_recv_acct_prod_name , "
            + "  '' gopay_out_order_id, "
            + "  (case "
            + "   when t1.PAY_CUST_ID is not null then "
            + "   (select t3.EMAIL "
            + "      from GOPAY_ALL_CUST_INFO t3 "
            + "   where t1.PAY_CUST_ID = t3.CUST_ID "
            + "    ) "
            + "    else "
            + "  '' "
            + " end) pay_cus_email, "
            + "  t1.PAY_ACCT pay_acct, "
            + " (case "
            + "   when t1.MAIN_RECV_CUST_ID is not null then "
            + "   (select t4.CUST_NAME "
            + "     from GOPAY_ALL_CUST_INFO t4 "
            + "   where t1.MAIN_RECV_CUST_ID = t4.CUST_ID "
            + "      ) "
            + "    else "
            + "    '' "
            + "  end) rec_cus_name, "
            + "  (case "
            + "   when t1.MAIN_RECV_CUST_ID is not null then "
            + "   (select t4.EMAIL "
            + "      from GOPAY_ALL_CUST_INFO t4 "
            + "     where t1.MAIN_RECV_CUST_ID = t4.CUST_ID "
            + "     ) "
            + "  else "
            + "  '' "
            + "  end) rec_cus_mail, "
            + " t1.MAIN_RECV_ACCT main_recv_acct, "
            + "  (case when t1.PAY_ACCT is not null then (select acc.PROD_CODE from ACPS_ACCT_MAST acc where acc.ACCT_ID=t1.PAY_ACCT)else '' end )acct_type, "
            + "  t1.GOPAY_INT_TXN_CD GOPAY_INT_TXN_CD, "
            + "  t1.GATEWAY_SOURCE order_ori, "
            + "  t1.TXN_STA_CD trans_status, "
            + "  '' hander_status, "
            + "  '' options, "
            + "  t1.CREATED_TS CREATED_TS, "
            + "  (case  when t1.PAY_CUST_ID is not null then (select t0.CUST_NAME from GOPAY_ALL_CUST_INFO t0 where t1.PAY_CUST_ID= t0.CUST_ID) else  ''  end)pay_cus_name  , "
            + "  -1 step_num,   "//
            + "  -1  decison,   "//
            + "  -1  done ,  "//
            + "  t1.PAY_CHANNEL  pay_channel ,  "//
            + "  t1.REVERSAL_FLAG  reversal_flag   "//
           // + "  t1.MER_ORDER_ID  MER_ORDER_ID  "//
            + "   from CPS_GEN_MAIN_ORDER  t1, "
            + "      GOPAY_ALL_CUST_INFO t5 "                
            + "   where 1=1 "
            + "    and t1.MER_ID = t5.CUST_ID "                            
            + "    and t1.GOPAY_INT_TXN_CD in('00901','09802','09803','00207') "           
            + "     and t1.REVERSAL_FLAG=100 "           
            + "  ))tt where 1=1  ";
	//需要审核的交易>>>>>>具备订单处理状态>>>>>>>提现和付款到银行,线下充值需要审核。<< 退款,担保支付取消（需要审核）现在辅助类的交易只有退款和担保支付取消交易   >>portal发起的付款到国付宝且付款方式是企业虚拟账户转账的才需要审核>>暂时的
	private static final String auditTranSql=
             "select * from ( (select t1.GOPAY_TXN_TM GOPAY_TXN_TM, "
            + "          t1.ORDER_STLM_TIME order_stlm_time, "
            + "         t1.MER_ORDER_ID mer_order_id, "
            + "        t1.GOPAY_ORDER_ID gopay_order_id, "
            + "         t5.cust_name trans_adr, "
            + "        t1.MER_ID mer_id, "
            + "       t1.MER_TXN_AMT order_deal_amt, "
//            + "       t1.ORDER_REAL_FEE_AMT order_real_fee_amt, "
            +"       DECODE(T1.GOPAY_INT_TXN_CD,"
            +"         '01200',"
            +"        T1.ORDER_REAL_FEE_AMT - (SELECT t6.refund_fee_amt FROM cps_gen_assist_order t6 WHERE t6.gopay_order_id=t1.gopay_order_id),"
            +"       T1.ORDER_REAL_FEE_AMT) ORDER_REAL_FEE_AMT, "
            + " (case "
            +"        when t1.OUT_STLM_ID is not null and t1.GOPAY_INT_TXN_CD  not in('00500') then "
            +"         (select b1.bank_name "
            + "            from bis_bank_info_main b1 "
            +"          where t1.OUT_STLM_ID = b1.BANK_CODE) "
            + "       when t1.gopay_order_id is not null and t1.pay_channel in('01','03','04','51') and t1.GOPAY_INT_TXN_CD  not in('00500')"
           
           //-----------------------------------------------
            //edit by fanghw 20140411
            //临时解决银行订单表同一国付宝订单存在2条以上记录问题 
            
            /*
           +"        then (select (select bis2.BANK_NAME from bis_bank_info_main bis2 where  bis1.GOPAY_BANK_CODE=bis2.bank_code  ) from bis_bank_main_order bis1 where t1.gopay_order_id=bis1.gopay_order_id)"
            */
            
            + " then ( "
            + " select  bis2.BANK_NAME "
            + " from bis_bank_main_order b10 "
            + " left join bis_bank_info_main bis2 on b10.GOPAY_BANK_CODE = bis2.bank_code "
            + " where  "
            + " not exists (select 1 from bis_bank_main_order bmo where bmo.created_ts>b10.created_ts and b10.gopay_order_id=bmo.gopay_order_id ) "
            + " and t1.GOPAY_ORDER_ID = b10.GOPAY_ORDER_ID "
            + " ) "
            // end edit
            //-----------------------------------------------
            
           
            
            + "      when t1.gopay_order_id is not null and t1.pay_channel not in('01','03','04','51') and t1.GOPAY_INT_TXN_CD  not in('00500') "
            +"       then (select (select info3.bank_name from bis_bank_info_main info3 where info3.bank_code=pay1.OFFLINE_GOPAY_BANK_CODE) from CPS_GEN_PAY_ORDER pay1 where t1.gopay_order_id=pay1.gopay_order_id ) "
            + "     else  "
            +"      '' "
            +"  end) main_pay_acct, "
            +"(case                  "
            +"       when t1.GOPAY_ORDER_ID is not null and  t1.GOPAY_INT_TXN_CD in('00500') then "
            +"       (select b11.RECV_BANK_NAME "
            +"          from CPS_GEN_PAY_ORDER b11 "
            +"        where t1.GOPAY_ORDER_ID = b11.GOPAY_ORDER_ID)"
            +"     else  "
            +"     ''  "
            +"  end)main_recv_acct_prod_name , "          
            +" (case "
            +"        when  t1.GOPAY_INT_TXN_CD not in('01200','01201','01203') then "
            
           //-----------------------------------------------
            //edit by fanghw 20140411
            //临时解决银行订单表同一国付宝订单存在2条以上记录问题 
            
            /*
           +"         (select   b10.GOPAY_OUT_ORDER_ID "
            +"           from bis_bank_main_order b10 "
            + "         where t1.GOPAY_ORDER_ID = b10.GOPAY_ORDER_ID and b10.ORDER_STATUS in('00','02','03','04') )  "
            */
            
            + " ( "
            + " select  b10.GOPAY_OUT_ORDER_ID "
            + " from bis_bank_main_order b10 "
            + " where  "
            + " b10.ORDER_STATUS in ('00', '02', '03', '04') "
            + " and not exists (select 1 from bis_bank_main_order bmo where bmo.created_ts>b10.created_ts and b10.gopay_order_id=bmo.gopay_order_id ) "
            + " and t1.GOPAY_ORDER_ID = b10.GOPAY_ORDER_ID "
            + " ) "
            // end edit
            //-----------------------------------------------
            
            
            
            
            
            
            + "         when  t1.GOPAY_INT_TXN_CD  in('01200','01201','01203') then "
            + "          (select a1.REFUND_BANK_ORDER_NUM from "
            +  "       CPS_GEN_ASSIST_ORDER a1  "
            + "        where t1.GOPAY_ORDER_ID=a1.GOPAY_ORDER_ID) "
            +"  else "
            +"  '' "
            + " end) gopay_out_order_id, "
            + " (case "
            + "        when t1.PAY_CUST_ID is not null and t1.GOPAY_INT_TXN_CD not in ('00500','00402','09900','09901','01200','01201','01203') then "
            + "         (select t3.EMAIL "
            + "            from GOPAY_ALL_CUST_INFO t3 "
            + "           where t1.PAY_CUST_ID = t3.CUST_ID) "
            + "        else "
            + "         '' "
            + "      end) pay_cus_email, "
            //+ "  t1.PAY_ACCT pay_acct, "
            + " (case "
            + "        when t1.PAY_ACCT is not null and "
            + "             t1.GOPAY_INT_TXN_CD not in ('00500','00402','09900','09901','01200','01201','01203') then "
            + "         t1.PAY_ACCT "
            + "         when t1.GOPAY_INT_TXN_CD in('00402','09900','09901','01200','01201','01203') then "
            + "          ( select rel.ACCT_ID  from ACPS_SCHEMA_ACCT_REL rel where rel.GOPAY_INT_TXN_CD=t1.GOPAY_INT_TXN_CD and rel.SCHEMA_CODE='2202') "      
            + "        else "
            + "         '' "
            + "      end) pay_acct, "
            + " (case "
            + "    when t1.MAIN_RECV_CUST_ID is not null and t1.GOPAY_INT_TXN_CD not in('00700','00800') then  "
            + "    (select t4.CUST_NAME  "
            + "    from GOPAY_ALL_CUST_INFO t4  "
            + "    where t1.MAIN_RECV_CUST_ID = t4.CUST_ID)  "
            + "     when  t1.GOPAY_ORDER_ID is not null and t1.GOPAY_INT_TXN_CD  in('00700','00800') then  "
            + "    (select pay1.RECV_BANK_ACCT_NAME from CPS_GEN_PAY_ORDER pay1 where t1.GOPAY_ORDER_ID =pay1.GOPAY_ORDER_ID)  "
            + "     else  "
            + "      ''  " 
            + "      end) rec_cus_name,  "           
            + "  (case "
            + "         when t1.MAIN_RECV_CUST_ID is not null and t1.GOPAY_INT_TXN_CD not in ('00700','00800') then "
            + "          (select t4.EMAIL  "
            + "             from GOPAY_ALL_CUST_INFO t4  "
            + "             where t1.MAIN_RECV_CUST_ID = t4.CUST_ID)     "                
            + "          else  "
            + "          ''  "
            + "        end) rec_cus_mail, "
            + "  (case   "
            + "         when t1.MAIN_RECV_ACCT is not null and t1.GOPAY_INT_TXN_CD not in ('00700','00800') then  "
            + "         (t1.MAIN_RECV_ACCT) "
            + "            when  t1.GOPAY_ORDER_ID is not null and t1.GOPAY_INT_TXN_CD  in('00700','00800') then "
            + "               (select pay1.RECV_BANK_ACCT_NUM from CPS_GEN_PAY_ORDER pay1 where t1.GOPAY_ORDER_ID =pay1.GOPAY_ORDER_ID) "
            + "         else  "
            + "          '' "
            + "       end)main_recv_acct, "
            + "  (case "
            + "         when t1.PAY_ACCT is not null and t1.GOPAY_INT_TXN_CD not in ('00500','00402','09900','09901','01200','01201','01203') then "
            + "          (select acc.PROD_CODE  "
            + "             from ACPS_ACCT_MAST acc "
            + "            where acc.ACCT_ID = t1.PAY_ACCT) "
            + "            when t1.GOPAY_INT_TXN_CD in ('00402','09900','09901','01200','01201','01203') then( "
            + "            select  (select ma1.PROD_CODE from ACPS_ACCT_MAST ma1 where ma1.ACCT_ID=rel.ACCT_ID)  from ACPS_SCHEMA_ACCT_REL rel where rel.GOPAY_INT_TXN_CD=t1.GOPAY_INT_TXN_CD and rel.SCHEMA_CODE='2202' " 
            + "              ) "
            + "         else "
            + "          '' "
            + "       end) acct_type,"
            + "  t1.GOPAY_INT_TXN_CD GOPAY_INT_TXN_CD, "
            + "  t1.GATEWAY_SOURCE order_ori, "
            + "  t1.TXN_STA_CD trans_status, "
            + "  '' hander_status, "
            + "  '' options, "
            + "  t1.CREATED_TS CREATED_TS, "
            + " (case "
            + "        when t1.PAY_CUST_ID is not null and t1.GOPAY_INT_TXN_CD not in ('00500','00402','09900','09901','01200','01201','01203') then "
            + "         (select t0.CUST_NAME "
            + "            from GOPAY_ALL_CUST_INFO t0 "
            + "            where t1.PAY_CUST_ID = t0.CUST_ID) "
            + "            when t1.GOPAY_INT_TXN_CD in('00402','09900','09901','01200','01201','01203') then(     "
            + "             select (select  ma1.ACCT_NAME from ACPS_ACCT_MAST ma1 where ma1.ACCT_ID=rel.ACCT_ID)  from ACPS_SCHEMA_ACCT_REL rel where rel.GOPAY_INT_TXN_CD=t1.GOPAY_INT_TXN_CD and rel.SCHEMA_CODE='2202'  "
            + "              ) "
            + "        else "
            + "         '' "
            + "     end) pay_cus_name, "
            + "  wf.STEP_NUM  step_num ,   "//
            + "  wf.DECISION  decision,   "//
            + "  wf.DONE   done,  "//
            + "  t1.PAY_CHANNEL  pay_channel ,  "//
            + "  t1.REVERSAL_FLAG  reversal_flag  "//
           // + "  t1.MER_ORDER_ID  MER_ORDER_ID  "//
            + "   from CPS_GEN_MAIN_ORDER  t1, "
          //  + "      CPS_GEN_PAY_ORDER     t2, "
            + "      GOPAY_ALL_CUST_INFO t5, "                
            + "      WF_APPR_VIEW wf "      
            + "   where  t1.MER_ID = t5.CUST_ID "             
            + "   and t1.gopay_order_id = wf.TARGET_ID     "
            + "   and wf.TARGET_TYPE = '1'     "//
           // + "   and t1.GOPAY_INT_TXN_CD in ('00700','00800')     "//
            + "   and wf.APPROVAL_ID <>1     "//  
            + " and not exists(select * from  WF_APPR_VIEW v0 where "
            + "       ( select count(v.TARGET_ID)from WF_APPR_TASK_DTL  v where v0.TARGET_ID=v.TARGET_ID  GROUP by v.TARGET_ID )>1 " 
            + "    and v0.TARGET_ID=wf.TARGET_ID and wf.APPROVAL_ID=2  and wf.TARGET_TYPE = 1 ) "
            
            +" and t1.GOPAY_INT_TXN_CD not in('00207') "
            + "  ))tt where 1=1  ";
	
    private static final String payOrRecieveSql(){
        StringBuffer finalString = new StringBuffer();
        finalString.append(" select * from ( ")
                   .append(revMoneySql)
                   .append(" UNION ALL ")
                   .append(payMoneySql)
                   .append(" UNION ALL ")
                   .append(transferAcctSql)
                   .append(" UNION ALL ")
                   .append(auditTranSql)
                   .append(" UNION ALL ")
                   .append(assistSql)
                   .append(" )ttt")
                   .append(" where 1=1");
        
        return finalString.toString();
    } 
	public List<Object[]> getTransList(Map map, int beginPage, int pageSize) {
	    Map<String ,Object> param= new HashMap<String, Object>();
		List<Object[]> list = findBySql(getSql(map,param),param, beginPage, pageSize);
		return list;
	}

	private String getSql(Map map,Map<String ,Object> param) {
		StringBuffer sql = new StringBuffer();		
			sql.append(payOrRecieveSql());
			sql.append(getFromWhereR( map,param));		
		sql.append(" order by ttt.CREATED_TS desc");
		return sql.toString();
	}

	@SuppressWarnings("unchecked")
	@Override
	public int getCount(Map map) {
        Map<String ,Object> param= new HashMap<String, Object>();
		StringBuffer sql = new StringBuffer();
		sql.append(" select count(1) from ( ");
		sql.append(getSql(map,param)).append(" )");
		return findCountBySql(sql.toString(), param);
	}

	private String getFromWhereR( Map map,Map<String ,Object> param) {
	    StringBuffer sql= new StringBuffer();
		// 客户订单号
		if (null != map.get("custNum") && !"".equals(map.get("custNum"))) {		   
                sql.append(" and    lower(ttt.MER_ORDER_ID) =  lower(:custNum) ");                
                param.put("custNum",((String) map.get("custNum")).trim());
          		
		}
		// 国付宝交易号
		if (null != map.get("changeNum") && !"".equals(map.get("changeNum"))) {
		 
                sql.append(" and    lower(ttt.GOPAY_ORDER_ID)  =  lower(:changeNum ) ");
               
                param.put("changeNum",((String) map.get("changeNum")).trim());
              
		}
		// 交易场所
		if (null != map.get("changeAdrr")
				&& !"".equals(map.get("changeAdrr"))) {
		    if(checkSQLLike((String)map.get("changeAdrr"))){                   
                sql.append(" AND lower(ttt.trans_adr) LIKE lower(:changeAdrr)").append("  escape '/' ");
                param.put("changeAdrr",(replaceSQLLike((String)map.get("changeAdrr"))));
            }else{
                sql.append(" and  ttt.trans_adr  like :changeAdrr " );              
                param.put("changeAdrr", "%"+((String)map.get("changeAdrr")).trim()+"%");
            }       	
		}
		
		  //账号类型 
		if (null != map.get("accType") &&
		 !"".equals(map.get("accType"))) {
		  sql.append(" and ttt.acct_type = :accType ");
          param.put("accType",((String) map.get("accType")).trim());
		  }
		 
		// 交易状态
		if (null != map.get("changeStatus")
				&& !"".equals(map.get("changeStatus"))) {
			sql.append(handerTransStatus((String)map.get("changeStatus")));
		}
		// 银行订单号
		if (null != map.get("bankOrder") && !"".equals(map.get("bankOrder"))) {		   
                sql.append(" and    lower(ttt.GOPAY_OUT_ORDER_ID)  =  lower(:bankOrder) ");
                param.put("bankOrder",((String) map.get("bankOrder")).trim());
               
                    
		}
		// 订单来源
		if (null != map.get("payChannel") && !"".equals(map.get("payChannel"))) {
			sql.append(" and  ttt.pay_channel = :payChannel ");
			param.put("payChannel",((String) map.get("payChannel")).trim());
		    //sql.append(handerOrderOri((String)map.get("orderOri")));
		}
		// 交易类型ctype
		if (null != map.get("ctype") && !"".equals(map.get("ctype"))) {
			sql.append(" and   ttt.GOPAY_INT_TXN_CD in ( ")
					.append(handerString((String) map.get("ctype")))
					.append(")");
		}
		// 收款方银行名称
        if (null != map.get("revBankName")
                && !"".equals(map.get("revBankName"))) {            
            if(checkSQLLike((String)map.get("revBankName"))){                   
                sql.append(" AND lower(ttt.main_recv_acct_prod_name) LIKE lower(:revBankName)").append("   escape '/' ");
                param.put("revBankName",(replaceSQLLike((String)map.get("revBankName"))));
            }else{
                sql.append(" and    lower(ttt.main_recv_acct_prod_name)  like  lower(:revBankName) ");
                param.put("revBankName", "%"+((String)map.get("revBankName")).trim()+"%");
                
            }                
        }
		// 收款客户名称
		if ((null != map.get("recCusName") && !"".equals(map
				.get("recCusName")))) {
		    
		    if(checkSQLLike((String)map.get("recCusName"))){                   
                sql.append(" AND lower(ttt.rec_cus_name) LIKE lower(:recCusName)").append("   escape '/' ");
                param.put("recCusName",(replaceSQLLike((String)map.get("recCusName"))));
            }else{
                sql.append(" and    lower(ttt.rec_cus_name)  like  lower(:recCusName) ");
                param.put("recCusName", "%"+((String)map.get("recCusName")).trim()+"%");
                
            }   	
		}
		// 付款客户名称
		if ((null != map.get("payCustName") && !"".equals(map
				.get("payCustName")))) {		    
		    if(checkSQLLike((String)map.get("payCustName"))){                   
                sql.append(" AND lower(ttt.pay_cus_name) LIKE lower(:payCustName)").append("   escape '/' ");
                param.put("payCustName",(replaceSQLLike((String)map.get("payCustName"))));
            }else{
                sql.append(" and    lower(ttt.pay_cus_name)  like  lower(:payCustName) ");
                param.put("payCustName", "%"+((String)map.get("payCustName")).trim()+"%");
                
            }       		
		}
		// 外部交易时间
		if (null != map.get("fromTranFinishTime")
				&& !"".equals(map.get("fromTranFinishTime"))) {
			sql.append(
					" and to_char( ttt.GOPAY_TXN_TM ,'yyyy-mm-dd') >=:fromTranFinishTime ");
			param.put("fromTranFinishTime",((String) map.get("fromTranFinishTime")).trim());
					
		}
		if (null != map.get("endTranfinishTime")
				&& !"".equals(map.get("endTranfinishTime"))) {
			sql.append(
					" and to_char( ttt.GOPAY_TXN_TM,'yyyy-mm-dd') <=:endTranfinishTime  ");
			param.put("endTranfinishTime",((String) map.get("endTranfinishTime")).trim());
					
		}
		// 清算时间时间
		if (null != map.get("fromOrderStlmTime")
				&& !"".equals(map.get("fromOrderStlmTime"))) {
			sql.append(
					" and to_char(ttt.ORDER_STLM_TIME,'yyyy-mm-dd') >=:fromOrderStlmTime ");
			param.put("fromOrderStlmTime",((String) map.get("fromOrderStlmTime")).trim());
					
		}
		if (null != map.get("endOrderStlmTime")
				&& !"".equals(map.get("endOrderStlmTime"))) {
			sql.append(
					" and  to_char(ttt.ORDER_STLM_TIME,'yyyy-mm-dd')  <=:endOrderStlmTime");
			param.put("endOrderStlmTime",((String) map.get("endOrderStlmTime")).trim());
					
		}
		return sql.toString();
	}


    private String handerTransStatus(String statuscode){
    	StringBuffer sb = new StringBuffer(); 
    	if(null!=statuscode&& !"".equals(statuscode.trim())){
    		if (statuscode.contains(",")){
    			sb.append(" AND (ttt.trans_status between 20001 and 20003  ) ");
    		}else if(String.valueOf(TxnStaCode.TXN_STA_CD_20000.value).equals(statuscode)){
    			sb.append(" and ttt.trans_status=20000 ");
    		}else {
    			sb.append(" and (( ttt.trans_status between 30000 and 30202 ) OR ttt.trans_status>=90000 ) ");
    		}
    		
    	}else {return "";}
    	return sb.toString();
    } 
    
    private String handerOrderOri(String code){
        StringBuffer sb = new StringBuffer(); 
        if(null!=code&& !"".equals(code.trim())){
            if (code.contains(",")){
                sb.append(" AND (ttt.order_ori in('01','02','03')) ");//订单来源网关
            }else if("04".equals(code.trim())) {
                sb.append(" and ttt.order_ori ='04' ");//订单来源移动支付
            }else if ("00".equals(code.trim())){
                sb.append(" and ttt.order_ori ='00' ");//未经网关
            }else{}
            
            
        }else {return "";}
        return sb.toString();
    }
    
	private String handerString(String ctype) {
		String[] strs = null;
		String str = "";
		StringBuffer sb = new StringBuffer();
		if (ctype != null && ctype.length() > 0) {
			strs = ctype.split(",");
		}
		if (strs.length > 0) {
			for (int i = 0; i < strs.length; i++) {
				sb.append("'").append(strs[i]).append("', ");
			}
		}
		int endindex = sb.toString().lastIndexOf(",");
		if (endindex > 0) {
			str = sb.toString().substring(0, endindex);
			return str;
		}
		str = sb.toString().substring(0);
		return str;
	}
    /* (non-Javadoc)
     * @see com.gopay.console.dao.trans.MainOrderDAO#getTransList(java.util.Map)
     */
    @Override
    public List<Object[]> getTransList(Map map) {
        Map<String ,Object> param= new HashMap<String, Object>();
        List<Object[]> list = findBySql(getSql(map,param),param);
        return list;
    }

	/*
	 * 订单类型为付款类 从国付宝到银行 不包括虚拟账户之间的转账 为了检查所得订单类型为付款类
	 * 1.付款到银行
	 * 2.提现
	 * 3.付款到国付宝
	 * 4.付款到关联账户
	 * 5.大宗出金
	 * 6.虚拟账户担保转账申请
	 */
	/*private Map<String, String> getOrderStranTypeGP() {
		Map<String, String> values = new HashMap<String, String>();
		values.put(IntTxnCd._00700.name(), IntTxnCd._00700.value);
		values.put(IntTxnCd._00800.name(), IntTxnCd._00800.value);
		values.put(IntTxnCd._00900.name(), IntTxnCd._00900.value);
		values.put(IntTxnCd._00901.name(), IntTxnCd._00901.value);
		values.put(IntTxnCd._00202.name(), IntTxnCd._00202.value);
		values.put(IntTxnCd._01000.name(), IntTxnCd._01000.value);

		return values;
	}*/
    
    private static List<String> specs = new ArrayList<String>();
    static {
        specs.add("_");
       // specs.add("-");
        specs.add("%");
       // specs.add("*");
       // specs.add("[");
        //specs.add("^");
    }
    
    public static boolean checkSQLLike(String str) {
        boolean check = false;
        for(String spec : specs) {
            if(str.indexOf(spec) > -1) {
                str = str.replace(spec, "/" + spec);
                check = true;
                break;
            }
            
        }
        return check;

    }
    
    public static String replaceSQLLike(String str) {

        for(String spec : specs) {
            if(str.indexOf(spec) > -1) {
                str = str.replace(spec, "/" + spec);

            }
            
        }
        str = "'%"+str+"%' ";
        return str;
    }
    /* (non-Javadoc)
     * @see com.gopay.console.dao.trans.MainOrderQueryDAO#getRepeatCount(java.lang.String, java.lang.String)
     */
    @Override
    public int getRepeatCount(String merId, String merOrderId) {
       String sql="select * from  CPS_GEN_MAIN_ORDER where  MER_ID=:merId and MER_ORDER_ID=:merOrderId";
       Map<String, Object> param = new HashMap<String, Object>();
       param.put("merId", merId);
       param.put("merOrderId", merOrderId);
      List<Object[]>lists= findBySql(sql, param);
      int count=lists.size();
        return count;
    }
    
    
    /**
	 * 钱包交易订单失效轮询打补丁
	 * 查询手机钱包快捷支付、wap网银支付进行中的订单
	 * @return gopay_order_id
	 * @author tangyang
	 * @date 20170526
	 * ps:后期可加上m.created_ts大于某个时间。因第一次需要查询存量所以订单，所以没加
	 */
	public List<Object[]> findWalletTimeoutOrderBank() {
		String sql =  "select m.gopay_order_id , 1, "//0 支付订单国付宝订单号
				 + "m.gopay_int_txn_cd"//add by zhoupeng 20170710
//				+" m.txn_sta_cd, "//1 支付订单状态
//				+" r.gopay_order_id, "//2 支付订单对应的00500订单号
//				+" r.txn_sta_cd, "//3 支付订单对应的00500订单状态（部分钱包交易为先充值后支付，存在两笔订单）
//				+" r.err_msg, "//4 00500订单错误原因
//				+" mb.gopay_out_order_id, "//5 支付订单对应银行主订单号
//				+" mb.order_status, "//6 支付订单对应银行主订单状态
//				+" mb.send_bank_tm, "//7 支付订单对应银行主订单发往银行时间
//				+" rb.gopay_out_order_id, "//8 支付订单对应的00500订单对应银行主订单号
//				+" rb.order_status, "//9 支付订单对应的00500订单对应银行主订单状态
//				+" rb.send_bank_tm "//10 支付订单对应的00500订单对应银行主订单发往银行时间
				+" from cps_gen_main_order m "
//				+" left join cps_gen_main_order r on m.gopay_order_id = r.relevance_gopay_order_id "//支付订单对应的00500订单
//				+" left join bis_bank_main_order mb on mb.gopay_order_id = m.gopay_order_id "//支付订单对应银行主订单
//				+" left join bis_bank_main_order rb on rb.gopay_order_id = r.gopay_order_id "//支付订单对应的00500订单对应银行主订单
				+" where m.gopay_int_txn_cd in ('01601','01603','01605','01606','01607','01608','01611','01612','01613','01618')  "//增加01618 add by zhoupeng 20170710   增加01601 20171024 add shao 
				+" and m.pay_channel in ('03','06','08') "
				+" and m.txn_sta_cd='30000' "
				+" and m.gateway_source = '05' "
				+" and m.GOPAY_TXN_TM < sysdate -1 ";
		logger.info(sql);
		List<?> result = findBySql(sql);
		return (List<Object[]>) result;
	}
	
	/**
	 * 钱包交易订单失效轮询打补丁
	 * 查询手机钱包余额支付进行中的订单
	 * @return gopay_order_id
	 * @author tangyang
	 * @date 20170526
	 * ps:后期可加上m.created_ts大于某个时间。因第一次需要查询存量所以订单，所以没加
	 */
	public List<Object[]> findWalletTimeoutOrderGfb() {
		String sql = " select m.gopay_order_id,"//0国付宝订单号
				 + "count(d.gopay_order_id),"//1账务明细表记录条数
				  + "m.gopay_int_txn_cd"//add by zhoupeng 20170710
				 + " from cps_gen_main_order m"
				 + " left join acps_acct_trans_dtl d on d.gopay_order_id = m.gopay_order_id"
				 + " where "
				 + " m.gopay_int_txn_cd in ('00800','01601','01603','01605','01606','01607','01608','01611','01612','01613','01618')  "//增加01618 add by zhoupeng 20170710   增加01601 20171024 add shao
				 + " and m.pay_channel in ('02','52')"
				 + " and m.txn_sta_cd='30000' "
				 + " and m.gateway_source = '05' "
				 + " and m.GOPAY_TXN_TM < sysdate-1"
				 + " group by m.gopay_order_id,m.gopay_int_txn_cd having count(d.gopay_order_id)=0";

		List<?> result = findBySql(sql);
		return (List<Object[]>) result;
		
		
	}
	/**
	 * 定时任务-手机钱包超时订单清理 根据超时时间获取除银行订单以外的超时订单
	 *
	 * @param timeSpan
	 * @return
	 */
	@Override
	public List<Object[]> findWalletTimeoutCpsGenMainOrderByTimeSpan() {
		String sql = " select t.gopay_order_id,t.relevance_gopay_order_id from cps_gen_main_order t " +
				" where t.txn_sta_cd in (30000,30101) " +
				" and t.gopay_int_txn_cd = '00500' " +
				" and t.pay_channel in ('03','06','08','10')" +
				" and t.gateway_source = '05' " +
				" and ROUND(TO_NUMBER(SYSDATE - (t.CREATED_TS+0))*24) >= 12 " +
				" union " +
				" select t.gopay_order_id,t.relevance_gopay_order_id from cps_gen_main_order t " +
				" where  t.gateway_source = '05' " +
				" and t.relevance_gopay_order_id is not null " +
				" and t.pay_channel in ('03','06','08','10') " +
				" and t.txn_sta_cd in (90101) " +
				" and t.gopay_int_txn_cd = '00500' " +
				" and t.created_ts > sysdate -2 ";
		List<?> result = findBySql(sql);
		return (List<Object[]>) result;
	}
}
